package com.open.exam.mapper;

import com.open.exam.databasemodel.ExamReport;
import com.open.exam.databasemodel.FreeExamReport;
import com.open.exam.dto.ExamReportDTO;
import com.open.exam.dto.ExamcaseViewModel;
import com.open.exam.dto.ExaminerSystateCount;
import com.open.exam.dto.ExamingMonitorViewModel;
import com.open.exam.sqlprovider.ExamReportProvider;
import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Map;

/**
 * @author 奶茶
 * Created by on 2017/11/28.
 */
@Mapper
public interface ExamReportMapper {

    @Select("SELECT * FROM ExamReport WHERE ExamPlanInfoID = #{examPlanInfoID} AND AppID = #{appID} AND ExamRoomID = #{examRoomID} AND ExamSceneID = #{examSceneID} AND ExamSubjectID = #{examSubjectID}")
    List<ExamReport> findExamReportList(@Param(value = "examPlanInfoID") String examPlanInfoID, @Param(value = "appID") String appID, @Param(value = "examRoomID") String examRoomID, @Param(value = "examSceneID") String examSceneID, @Param(value = "examSubjectID") String examSubjectID);

    @Select("SELECT * FROM ExamReport WHERE ExamPlanInfoID = #{examPlanInfoID} AND AppID = #{appID} AND ExamRoomID = #{examRoomID} AND ExamSceneID = #{examSceneID} AND ExamSubjectID = #{examSubjectID} LIMIT 1")
    ExamReport findFirstExamReport(@Param(value = "examPlanInfoID") String examPlanInfoID, @Param(value = "appID") String appID, @Param(value = "examRoomID") String examRoomID, @Param(value = "examSceneID") String examSceneID, @Param(value = "examSubjectID") String examSubjectID);

    @Insert("INSERT INTO ExamReport (\n" +
            "ExamReportID,AppID,ExamPlanInfoID,ExamSubjectID,ExamRoomID,ExamSceneID,ActuallyBeginTime,IfAccident,PaperVersion,ExamReportState,ExamReport,ExamResultState,ExamResult,IfUpload,UploadTime\n" +
            ") SELECT #{examReportID},#{appID},#{examPlanInfoID},#{examSubjectID},#{examRoomID},#{examSceneID},#{actuallyBeginTime},#{ifAccident},#{paperVersion},#{examReportState},#{examReport},#{examResultState},#{examResult},#{ifUpload},#{uploadTime}\n" +
            "FROM\n" +
            "\tDUAL\n" +
            "WHERE\n" +
            "\tNOT EXISTS (\n" +
            "\t\tSELECT\n" +
            "\t\t\t1\n" +
            "\t\tFROM\n" +
            "\t\t\tExamReport\n" +
            "\t\tWHERE\n" +
            "\t\t\tExamPlanInfoID = #{examPlanInfoID}\n" +
            "\t\tAND ExamSubjectID = #{examSubjectID}\n" +
            "\t\tAND ExamSceneID = #{examSceneID}\n" +
            "\t\tAND ExamRoomID = #{examRoomID}\n" +
            "\t\tLIMIT 1\n" +
            ")")
    Integer insertExamReport(ExamReport examReport);

    @Select("SELECT DISTINCT y.ExamineeName,y.ExamCardNo,x.IDCardResult,x.IDCardTime,x.IDCardReason \n" +
            "FROM ExamingMonitor AS x \n" +
            "JOIN Examinee AS y\n" +
            "ON x.ExamineeID = y.ExamineeID\n" +
            "WHERE x.AppID = #{appID} AND x.ExamPlanInfoID = #{examPlanInfoID} AND y.AppID = #{appID} AND y.ExamPlanInfoID = #{examPlanInfoID}")
    List<Map<String,Object>> getSignInReport(@Param(value = "appID") String appID, @Param(value = "examPlanInfoID") String examPlanInfoID);

    @SelectProvider(type = ExamReportProvider.class,method = "findExamReportListBySomeWhere")
    List<ExamcaseViewModel> findExamReportListBySomeWhere(@Param(value = "planId") String planId, @Param(value = "roomId") String roomId, @Param(value = "sceneId") String sceneId, @Param(value = "subjectId") String subjectId, @Param(value = "reportState") Integer reportState, @Param(value = "appId") String appId);

    @SelectProvider(type = ExamReportProvider.class,method = "findAbsenceOrAllCountList")
    List<Map<String,Object>> findAbsenceOrAllCountList(@Param(value = "planId") String planId, @Param(value = "roomId") String roomId, @Param(value = "sceneId") String sceneId, @Param(value = "subjectId") String subjectId, @Param(value = "appId") String appId, @Param(value = "sysState") Integer sysState);

    @SelectProvider(type = ExamReportProvider.class,method = "findExamingMonitorList")
    List<Map<String,Object>> findExamingMonitorList(@Param(value = "planId") String planId, @Param(value = "roomId") String roomId, @Param(value = "sceneId") String sceneId, @Param(value = "subjectId") String subjectId, @Param(value = "appId") String appId, @Param(value = "siteId") String siteId);

    @Select("SELECT * FROM ExamReport WHERE ExamReportID = #{reportId}")
    ExamReport getExamReportById(@Param(value = "reportId") Integer reportId);

    @Select("SELECT y.ExamPlanInfoID,y.ExamSceneID,z.CertificateNo,y.Photo FROM ExamineeArrange AS x\n" +
            "JOIN ExamReport AS r ON x.ExamPlanInfoID = r.ExamPlanInfoID\n" +
            "JOIN ExamineeExt AS y ON x.ExamineeID = y.ExamineeID\n" +
            "JOIN Examinee AS z ON y.ExamineeID = z.ExamineeID\n" +
            "WHERE r.AppID = #{appId} AND r.ExamPlanInfoID = #{planId} AND r.ExamReportID = #{reportId}\n" +
            "AND x.AppID = #{appId} AND x.ExamPlanInfoID = #{planId}\n" +
            "AND z.AppID = #{appId} AND z.ExamPlanInfoID = #{planId} AND z.ExamSiteID = #{siteId}")
    List<Map<String,Object>> findExamineePhotoBySomeWhere(@Param(value = "appId") String appId, @Param("planId") Integer planId, @Param("siteId") Integer siteId, @Param(value = "reportId") Integer reportId);

    @SelectProvider(type = ExamReportProvider.class,method = "findFreeExamReportList")
    List<Map<String,Object>> findFreeExamReportList(@Param(value = "appId") String appId, @Param(value = "planId") String planId, @Param(value = "subjectId") String subjectId, @Param(value = "siteId") String siteId, @Param(value = "examCardNo") String examCardNo, @Param(value = "examineeName") String examineeName);

    @Select("SELECT datali.ExamReportID,datali.AppID AppId,datali.ExamSubjectID,datali.ExamPlanInfoID,datali.ExamReport,datali.ExamResult,datali.IfUpload,datali.IncludeInfo,dataSub.SubjectName ExamSubjectName,dataRoom.ExamRoomID ExamRoomId,dataRoom.ExamRoomName\n" +
            "FROM ( SELECT * FROM FreeExamReport WHERE ExamPlanInfoID = #{planId} AND AppID = #{appId} ) AS datali\n" +
            "JOIN ExamSubject AS dataSub ON datali.ExamSubjectID = dataSub.ExamSubjectID\n" +
            "JOIN ExamRoom AS dataRoom ON datali.ExamReport = dataRoom.ExamRoomID")
    List<Map<String,Object>> findExitFreeReportList(@Param(value = "appId") String appId, @Param(value = "planId") String planId);

    @Select("SELECT * FROM FreeExamReport WHERE ExamSubjectID = #{examSubjectID} AND ExamReport = #{examReport} AND ExamPlanInfoID = #{examPlanInfoID}")
    FreeExamReport getFreeReport(@Param(value = "examSubjectID") String examSubjectID, @Param(value = "examReport") String examReport, @Param(value = "examPlanInfoID") String examPlanInfoID);

    @Select("SELECT y.ExamPlanInfoID,y.ExamSceneID,y.Photo,z.CertificateNo\n" +
            "FROM ExamineeArrange AS x\n" +
            "JOIN ExamineeExt AS y ON x.ExamineeID = y.ExamineeID\n" +
            "JOIN Examinee AS z ON y.ExamineeID = z.ExamineeID\n" +
            "WHERE x.AppID = #{appId} AND x.ExamPlanInfoID = #{planId} AND x.ExamSubjectID = #{subjectId}\n" +
            "AND z.AppID = #{appId} AND z.ExamPlanInfoID = #{planId} AND z.ExamSiteID = #{siteId}")
    List<Map<String,Object>> findFreePhoto(@Param(value = "appId") String appId, @Param("planId") Integer planId, @Param("siteId") Integer siteId, @Param(value = "subjectId") Integer subjectId);

    @Select("SELECT arrange.ExamineeArrangeID ID,arrange.ExamineeName,examinee.ExamCardNo,monitor.SysState,monitor.DelayMinute,monitor.ExamEndTime,arrange.ExamineeID,monitor.ExamStartTime LoginTime,monitor.ExamStartTime RelBeginTime,monitor.ExamSubjectID,monitor.DelayMinute DelayedTime,subject.SubjectName ExamSubjectName,subject.SubjectCode examSubjectCode,examPaper.ExamPaperID examPaperID,examPaper.PaperID sourceExamPaperID,arrange.ExamRoomID,arrange.ExamSceneID,monitor.IDCardResult Register,monitor.IDCardTime RegisterTime,monitor.IDCardReason Remark,arrange.PlanBeginTime,examinee.CertificateNo,plan.ExamPlanName ExamPlanInfoName,plan.DeploymentMode deploymentMode,scene.ExamSceneName,room.ExamRoomName,site.ExamSiteID,site.ExamSiteName\n" +
            "FROM \n" +
            "\t(SELECT * FROM ExamingMonitor WHERE AppID = #{appId} AND ExamPlanInfoID = #{planId} AND ExamSubjectID = #{subjectId} AND ExamineeID = #{examineeId}) AS monitor\n" +
            "\tJOIN ExamineeArrange AS arrange ON monitor.AppID = arrange.AppID AND monitor.ExamPlanInfoID = arrange.ExamPlanInfoID AND monitor.ExamSubjectID = arrange.ExamSubjectID AND monitor.ExamineeID = arrange.ExamineeID\n" +
            "\tJOIN ExamPlanInfo AS plan ON plan.ExamPlanInfoID = monitor.ExamPlanInfoID\n" +
            "\tJOIN ExamRoom AS room ON room.ExamRoomID = monitor.ExamRoomID\n" +
            "\tJOIN ExamSite AS site ON site.ExamSiteID = room.ExamSiteID\n" +
            "\tJOIN ExamScenes AS scene ON scene.ExamSceneID = monitor.ExamSceneID\n" +
            "\tJOIN ExamSubject AS subject ON subject.ExamSubjectID = monitor.ExamSubjectID\n" +
            "\tJOIN Examinee AS examinee ON examinee.ExamineeID = arrange.ExamineeID\n" +
            //"\tJOIN ExamineeExamPaper AS paper ON paper.AppID = monitor.AppID AND paper.ExamPlanInfoID = monitor.ExamPlanInfoID AND paper.ExamSubjectID = monitor.ExamSubjectID AND paper.ExamineeID = monitor.ExamineeID\n" +
            "\tJOIN ExamPaper AS examPaper ON examPaper.ExamPaperID = arrange.ExamPaperID \n" +
            "WHERE examinee.AccountType = 0 GROUP BY monitor.ExamPlanInfoID,monitor.ExamSubjectID \n")
    List<ExamingMonitorViewModel> findFreeExamingMoniterList(@Param(value = "appId") String appId, @Param(value = "subjectId") String subjectId, @Param(value = "planId") String planId, @Param(value = "sysState") Integer sysState, @Param(value = "examineeId") String examineeId);

    @Update("UPDATE ExamReport SET ExamResult = #{examResult},ExamReportState = #{examReportState},IfUpload = #{ifUpload} WHERE ExamReportID = #{examReportID}")
    Integer updateExamReport(ExamReport examReport);

    @SelectProvider(type = ExamReportProvider.class,method = "findFreeExamSubjectReportList")
    List<ExamReportDTO> findFreeExamSubjectReportList(@Param(value = "examPlanID") String examPlanID, @Param(value = "upload") Object upload, @Param(value = "examResultStatus") Object examResultStatus);

    @Select("SELECT SysState,COUNT(*) count FROM examingmonitor WHERE ExamPlanInfoID = #{examPlanID} AND ExamSubjectID = #{examSubjectID} GROUP BY SysState;")
    List<ExaminerSystateCount> findExaminerCountBYSystate(@Param(value = "examPlanID") String examPlanID, @Param(value = "examSubjectID") String examSubjectID);
}
